﻿using System;
using System.Collections;
using System.Runtime.InteropServices;
using System.Text;
//using SQLDMO;

namespace Framework
{
    public class DbManagementSql
    {
        private const short SqlHandleEnv = 1;
        private const short SqlHandleDbc = 2;
        private const int SqlAttrOdbcVersion = 200;
        private const int SqlOvOdbc3 = 3;
        private const short SqlSuccess = 0;
        private const short SqlNeedData = 99;
        private const short DefaultResultSize = 1024;
        private const string SqlDriverStr = "DRIVER=SQL SERVER";

        #region 获取数据库服务器列表

        ///// <summary>
        ///// 获取服务器列表
        ///// </summary>
        ///// <returns></returns>
        //public static ArrayList GetSqlServerList()
        //{
        //    var list = new ArrayList();
        //    var sqlApp = new Application();
        //    NameList serverName = sqlApp.ListAvailableSQLServers();

        //    for (int i = 0; i < serverName.Count; i++)
        //    {
        //        list.Add(serverName.Item(i + 1));
        //    }
        //    return list;
        //}

        [DllImport("odbc32.dll")]
        private static extern short SQLAllocHandle(short hType, IntPtr inputHandle, out IntPtr outputHandle);

        [DllImport("odbc32.dll")]
        private static extern short SQLSetEnvAttr(IntPtr henv, int attribute, IntPtr valuePtr, int strLength);

        [DllImport("odbc32.dll")]
        private static extern short SQLFreeHandle(short hType, IntPtr handle);

        [DllImport("odbc32.dll", CharSet = CharSet.Ansi)]
        private static extern short SQLBrowseConnect(IntPtr hconn, StringBuilder inString,
                       short inStringLength, StringBuilder outString,
                       short outStringLength,
                       out short outLengthNeeded);
        /// <summary>
        /// 获取服务器列表
        /// </summary>
        /// <returns></returns>
        public static string[] GetServers()
        {
            string[] retval = null;
            string txt = string.Empty;
            IntPtr henv = IntPtr.Zero;
            IntPtr hconn = IntPtr.Zero;
            var inString = new StringBuilder(SqlDriverStr);
            var outString = new StringBuilder(DefaultResultSize);
            var inStringLength = (short)inString.Length;
            try
            {
                if (SqlSuccess == SQLAllocHandle(SqlHandleEnv, henv, out henv))
                {
                    if (SqlSuccess == SQLSetEnvAttr(henv, SqlAttrOdbcVersion, (IntPtr)SqlOvOdbc3, 0))
                    {
                        if (SqlSuccess == SQLAllocHandle(SqlHandleDbc, henv, out hconn))
                        {
                            short lenNeeded;
                            if (SqlNeedData == SQLBrowseConnect(hconn, inString, inStringLength, outString,
                                        DefaultResultSize, out lenNeeded))
                            {
                                if (DefaultResultSize < lenNeeded)
                                {
                                    outString.Capacity = lenNeeded;
                                    if (SqlNeedData != SQLBrowseConnect(hconn, inString, inStringLength, outString,
                                                lenNeeded, out lenNeeded))
                                    {
                                        throw new ApplicationException("Unabled to aquire SQL Servers from ODBC driver.");
                                    }
                                }
                                txt = outString.ToString();
                                int start = txt.IndexOf("{", StringComparison.Ordinal) + 1;
                                int len = txt.IndexOf("}", StringComparison.Ordinal) - start;
                                if ((start > 0) && (len > 0))
                                {
                                    txt = txt.Substring(start, len);
                                }
                                else
                                {
                                    txt = string.Empty;
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                txt = string.Empty;
            }
            finally
            {
                if (hconn != IntPtr.Zero)
                {
                    SQLFreeHandle(SqlHandleDbc, hconn);
                }
                if (henv != IntPtr.Zero)
                {
                    SQLFreeHandle(SqlHandleEnv, hconn);
                }
            }

            if (txt.Length > 0)
            {
                retval = txt.Split(",".ToCharArray());
            }
            return retval;
        }

        #endregion
        private string _password; //用户密码
        private string _serverName; //数据服务器名称
        private string _userName; //用户名称
        private string _message; //消息提示  
        private int _pBar;   //状态条控件



        ///// <summary>
        ///// 取得数据库服务器列表
        ///// </summary>
        ///// <returns> 数据库服务器列表 </returns>
        //public ArrayList GetServerList()
        //{
        //    var alServers = new ArrayList();
        //    Application sqlApp = new ApplicationClass();
        //    try
        //    {
        //        NameList serverList = sqlApp.ListAvailableSQLServers();
        //        for (int i = 1; i <= serverList.Count; i++)
        //        {
        //            alServers.Add(serverList.Item(i));
        //        }
        //    }
        //    catch (Exception e)
        //    {
        //        _message = "取数据库服务器列表出错：" + e.Message;
        //    }
        //    finally
        //    {
        //        sqlApp.Quit();
        //    }
        //    return alServers;
        //}


        ///// <summary>
        ///// 错误消息处理
        ///// </summary>
        ///// <returns> 消息信息 </returns>
        //public string Msg()
        //{
        //    return _message;
        //}

        /////<summary>
        /////取得指定数据库列表
        ///// </summary>
        ///// <param name="strServerName"> 服务器名称 </param>
        ///// <param name="strUserName"> 用户名称 </param>
        ///// <param name="strPwd"> 用户密码 </param>
        ///// <returns> 数据库列表 </returns>
        //public ArrayList GetDbList(string strServerName, string strUserName, string strPwd)
        //{
        //    _serverName = strServerName;
        //    _userName = strUserName;
        //    _password = strPwd;
        //    var alDbs = new ArrayList();
        //    Application sqlApp = new ApplicationClass();
        //    SQLServer svr = new SQLServerClass();
        //    try
        //    {
        //        svr.Connect(_serverName, _userName, _password);
        //        foreach (Database db in svr.Databases)
        //        {
        //            if (db.Name != null)
        //                alDbs.Add(db.Name);
        //        }
        //    }
        //    catch (Exception err)
        //    {
        //        _message = "连接数据库出错：" + err.Message;
        //    }
        //    finally
        //    {
        //        svr.DisConnect();
        //        sqlApp.Quit();
        //    }
        //    return alDbs;
        //}


        /////<summary>
        ///// 数据库的备份和实时进度显示
        /////</summary>
        ///// <param name="strDbName">状态条控件名称 </param>
        ///// <param name="strFileName">pgbMain </param>
        /////<param name="pgb"> </param>
        /////<param name="strServerName">服务器名称 </param>
        ///// <param name="strUserName">用户名称 </param>
        ///// <param name="strPwd">密码 </param>
        ///// <returns> 备份成功返回true ，否则返回false </returns>
        //public bool BackUpdb(string strDbName, string strFileName, string strServerName, string strUserName, string strPwd, int pgb)
        //{
        //    _serverName = strServerName;
        //    _userName = strUserName;
        //    _password = strPwd;
        //    _pBar = pgb;
        //    SQLServer svr = new SQLServerClass();
        //    try
        //    {
        //        svr.Connect(_serverName, _userName, _password);
        //        Backup bak = new BackupClass();
        //        bak.Action = 0;
        //        bak.Initialize = true;
        //        BackupSink_PercentCompleteEventHandler pceh = Step;
        //        bak.PercentComplete += pceh;
        //        bak.Files = strFileName;
        //        bak.Database = strDbName;
        //        bak.SQLBackup(svr);
        //        return true;
        //    }
        //    catch (Exception err)
        //    {
        //        _message = "备份数据库失败：" + err.Message;
        //        return false;
        //    }
        //    finally
        //    {
        //        svr.DisConnect();
        //    }
        //}


        ///// <summary>
        ///// 显示进度条的当前进度
        ///// </summary>
        ///// <param name="message"></param>
        ///// <param name="percent"></param>
        //private void Step(string message, int percent)
        //{
        //    _pBar = percent;
        //}
        //// 其中，这两个语句实现了进度的实时显示：  
        //// SQLDMO.BackupSink_PercentCompleteEventHandler pceh = new SQLDMO.BackupSink_PercentCompleteEventHandler(Step);  
        //// bak.PercentComplete += pceh;  
        //// Step就是上面private void Step(string message,int percent) 的方法名称，它用来显示进度条的当前进度。  


        ///// <summary>
        ///// 数据库的恢复和杀死进程
        ///// </summary>
        ///// <param  name="strDbName"> 数据库名称 </param>
        ///// <param name="strFileName"> 备份文件名 </param>
        ///// <param name="strServerName"> 服务器名称 </param>
        ///// <param name="strUserName"> 用户名称 </param>
        ///// <param name="strPwd"> 密码 </param>
        ///// <returns> 恢复成功返回true ，否则返回false </returns>
        //public bool RestoreDb(string strDbName, string strFileName, string strServerName, string strUserName, string strPwd, int pgb)
        //{
        //    _pBar = pgb;
        //    SQLServer svr = new SQLServerClass();
        //    try
        //    {
        //        _serverName = strServerName;
        //        _userName = strUserName;
        //        _password = strPwd;
        //        svr.Connect(_serverName, _userName, _password);
        //        QueryResults qr = svr.EnumProcesses(-1);
        //        int iColPidNum = -1;
        //        int iColDbName = -1;
        //        for (int i = 1; i <= qr.Columns; i++)
        //        {
        //            string strName = qr.ColumnName[i];
        //            if (strName.ToUpper().Trim() == "SPID")
        //            {
        //                iColPidNum = i;
        //            }
        //            else if (strName.ToUpper().Trim() == "DBNAME")
        //            {
        //                iColDbName = i;
        //            }
        //            if (iColPidNum != -1 && iColDbName != -1)
        //                break;
        //        }
        //        for (int i = 1; i <= qr.Rows; i++)
        //        {
        //            int lPid = qr.GetColumnLong(i, iColPidNum);
        //            string strDBName = qr.GetColumnString(i, iColDbName);
        //            if (strDBName.ToUpper() == strDbName.ToUpper())
        //                svr.KillProcess(lPid);
        //        }
        //        Restore res = new RestoreClass();
        //        res.Action = 0;
        //        RestoreSink_PercentCompleteEventHandler pceh = Step;
        //        res.PercentComplete += pceh;
        //        res.Files = strFileName;
        //        res.Database = strDbName;
        //        res.ReplaceDatabase = true;
        //        res.SQLRestore(svr);
        //        return true;
        //    }
        //    catch (Exception err)
        //    {
        //        _message = "恢复数据库失败,请关闭所有和该数据库连接的程序！" + err.Message;
        //        return false;
        //    }
        //    finally
        //    {
        //        svr.DisConnect();
        //    }
        //}
    }
}